﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;
using PPI.Entities.Enumerator;
using PPI.Entities.Inspections;
using PPI.General;
using PPI.Mobile.Logic;
using VMind.Global;

namespace PPI.Client.Logic.Inspection
{
    public class LInspectionImage
    {
        #region MobileQuery

        public static Int32 CountMobileImages(string connectionString)
        {
            StringBuilder _sql = new StringBuilder();
            _sql.AppendLine("select count(*) FROM tb_Images");
            return (Int32) LMobileDatabase.ExecuteScalar(_sql.ToString(), connectionString);


        }

        public static List<EInspectionImage> GetAll(string connectionString)
        {

            StringBuilder _sql = new StringBuilder();

            #region Query
            _sql.AppendLine("SELECT ");
            _sql.AppendLine("   ImageId, ");
            _sql.AppendLine("   Filename, ");
            _sql.AppendLine("   UtilityInspectionFk, ");
            _sql.AppendLine("   InspectionServerID, ");
            _sql.AppendLine("   InspectionResultServerID, ");
            _sql.AppendLine("   ImageServerID, ");
            _sql.AppendLine("   UploadedDate, ");
            _sql.AppendLine("   UtilityInspectionResultFk ");
            _sql.AppendLine(" FROM tb_Images ");
            #endregion Query

            DataTable dt = LMobileDatabase.ExecuteQueryDataset(_sql.ToString(), connectionString).Tables[0];

            List<EInspectionImage> _InspectionImages = parseMobile(connectionString, dt);
            return _InspectionImages;

        }

        public static List<EInspectionImage> GetByInspection(string connectionString, Int32 inspectionId)
        {

            StringBuilder _sql = new StringBuilder();

            #region Query
            _sql.AppendLine("SELECT ");
            _sql.AppendLine("   ImageId, ");
            _sql.AppendLine("   Filename, ");
            _sql.AppendLine("   UtilityInspectionFk, ");
            _sql.AppendLine("   InspectionServerID, ");
            _sql.AppendLine("   InspectionResultServerID, ");
            _sql.AppendLine("   ImageServerID, ");
            _sql.AppendLine("   UploadedDate, ");
            _sql.AppendLine("   UtilityInspectionResultFk ");
            _sql.AppendLine(" FROM tb_Images ");
            _sql.AppendLine(" Where UtilityInspectionFk = " + inspectionId + "  ");
            #endregion Query

            DataTable dt = LMobileDatabase.ExecuteQueryDataset(_sql.ToString(), connectionString).Tables[0];

            List<EInspectionImage> _InspectionImages = parseMobile(connectionString, dt);
            return _InspectionImages;

        }

        private static List<EInspectionImage> parseMobile(string connectionString, DataTable dt)
        {
            List<EInspectionImage> _InspectionImages = new List<EInspectionImage>();
            DatabaseTypes _DatabaseType = LOutbox.GetDatabaseType(connectionString);

            foreach (DataRow dr in dt.Rows)
            {
                EInspectionImage _Image = new EInspectionImage();
                _Image.Id = ConvertHelper.ToInt32(dr["ImageId"]);
                _Image.ServerID = ConvertHelper.ToInt32(dr["ImageServerID"]);
                _Image.Inspection.DatabaseType = _DatabaseType;
                _Image.Inspection.Id = ConvertHelper.ToInt32(dr["UtilityInspectionFk"]);
                _Image.Inspection.ServerID = ConvertHelper.ToInt32(dr["InspectionServerID"]);
                _Image.InspectionResult.Id = ConvertHelper.ToInt32(dr["UtilityInspectionResultFk"]);
                _Image.InspectionResult.SeverId = ConvertHelper.ToInt32(dr["InspectionResultServerID"]);
                _Image.ImageFile = new FileInfo(DirHelper.GetImagesDirectory(connectionString) + Path.DirectorySeparatorChar + dr["Filename"].ToString());

                _InspectionImages.Add(_Image);

            }
            return _InspectionImages;
        }

        #region CRUD

        public static Int32 SaveMobile(EInspectionImage image, string connectionString)
        {
            Int32 returnId = image.Id;

            if(image.Id == Int32.MinValue)
            {
                image.Id = InsertMobile(image, connectionString);
                returnId = image.Id;

                string newImageName = image.Id + ".jpg";
                string newImagePath = DirHelper.CopyImageToOutbox(connectionString, image.ImageFile.FullName, newImageName);
                image.ImageFile = new FileInfo(newImagePath);
                UpdateFileName(newImageName, image.Id, connectionString);
            }
            return returnId;
        }

        private static Int32 InsertMobile(EInspectionImage image, string connectionString)
        {
            StringBuilder _Query = new StringBuilder();
            List<SqlParameter> listParameter = new List<SqlParameter>();

            #region Parameters
            listParameter.Add(DBHelper.CreateIntParameter("@UtilityInspectionFk", ParameterDirection.Input, false, image.Inspection.Id));
            listParameter.Add(DBHelper.CreateNVarCharParameter("@Filename", 255, ParameterDirection.Input, false, image.ImageFile.Name));

            if (image.InspectionResult.Id != Int32.MinValue)
                listParameter.Add(DBHelper.CreateIntParameter("@UtilityInspectionResultFk", ParameterDirection.Input, false, image.InspectionResult.Id));
            else
                listParameter.Add(DBHelper.CreateIntParameter("@UtilityInspectionResultFk", ParameterDirection.Input, true, DBNull.Value));

            #endregion

            #region Query

            _Query.AppendLine("Insert into tb_Images (Filename,UtilityInspectionFk,UtilityInspectionResultFk)");
            _Query.AppendLine(" values(@Filename,@UtilityInspectionFk,@UtilityInspectionResultFk)");


            #endregion

            LMobileDatabase.ExecuteSingleQueryWhithParameters(_Query.ToString(),connectionString,listParameter.ToArray());

            _Query = new StringBuilder("Select max(ImageId) from tb_Images ");

            return (Int32) LMobileDatabase.ExecuteScalar(_Query.ToString(), connectionString);


        }

        public static Int32 InsertMobileProject(EInspectionImage image, string connectionString)
        {
            if (image.Id == Int32.MinValue)
                image.Id = GetNextImageMobileId(connectionString);
            StringBuilder _Query = new StringBuilder();
            List<SqlParameter> listParameter = new List<SqlParameter>();

            #region Parameters
            listParameter.Add(DBHelper.CreateIntParameter("@ImageId", ParameterDirection.Input, false, image.Id));
            listParameter.Add(DBHelper.CreateIntParameter("@UtilityInspectionFk", ParameterDirection.Input, false, image.Inspection.Id));
            listParameter.Add(DBHelper.CreateNVarCharParameter("@Filename", 255, ParameterDirection.Input, false, image.ImageFile.Name));

            if (image.InspectionResult.Id != Int32.MinValue)
                listParameter.Add(DBHelper.CreateIntParameter("@UtilityInspectionResultFk", ParameterDirection.Input, false, image.InspectionResult.Id));
            else
                listParameter.Add(DBHelper.CreateIntParameter("@UtilityInspectionResultFk", ParameterDirection.Input, true, DBNull.Value));

            #endregion

            #region Query

            _Query.AppendLine("Insert into tb_Images (ImageId,Filename,UtilityInspectionFk,UtilityInspectionResultFk)");
            _Query.AppendLine(" values(@ImageId,@Filename,@UtilityInspectionFk,@UtilityInspectionResultFk)");


            #endregion

            LMobileDatabase.ExecuteSingleQueryWhithParameters(_Query.ToString(), connectionString, listParameter.ToArray());

            return GetNextImageMobileId(connectionString);
        }

        private static int GetNextImageMobileId(string connectionString)
        {
            StringBuilder _Query;
            _Query = new StringBuilder("Select max(ImageId) from tb_Images ");

            return (Int32)LMobileDatabase.ExecuteScalar(_Query.ToString(), connectionString);
        }

        public static void DeleteMobile(EInspectionImage image, string connectionString)
        {
            StringBuilder _Query = new StringBuilder("Delete tb_Images where ImageId = @ImageId ");
            List<SqlParameter> listParameters = new List<SqlParameter>();
            listParameters.Add(DBHelper.CreateIntParameter("@ImageId",ParameterDirection.Input,false,image.Id));

            LMobileDatabase.ExecuteSingleQueryWhithParameters(_Query.ToString(),connectionString,listParameters.ToArray());



        }

        public static void UpdateUploadedData(EInspectionImage image, string connectionString)
        {
            StringBuilder _query = new StringBuilder();

            _query.AppendLine("Update tb_Images set  InspectionResultServerID = " + image.InspectionResult.SeverId + ",InspectionServerID = " + image.Inspection.ServerID + ",UploadedDate = " + DateTime.Now.ToString("yyyy-MM-dd") + " ,ImageServerID =" + image.ServerID + " where ImageId =" + image.Id + " ");
            LMobileDatabase.ExecuteQuery(_query.ToString(), connectionString);

        }

        private static void UpdateFileName(string fileName, Int32 idImage, string connectionString)
        {
            StringBuilder _Query = new StringBuilder("Update tb_Images SET Filename = @FileName where ImageId = @ImageId  ");

            List<SqlParameter> listParameter = new List<SqlParameter>();
            listParameter.Add(DBHelper.CreateIntParameter("@ImageId", ParameterDirection.Input, false, idImage));
            listParameter.Add(DBHelper.CreateNVarCharParameter("@FileName", 255, ParameterDirection.Input, false, fileName));

            LMobileDatabase.ExecuteSingleQueryWhithParameters(_Query.ToString(), connectionString, listParameter.ToArray());



        }

        #endregion

        

        #endregion

        #region CRUD
        public static Int32 Save(EInspectionImage image)
        {
            image.Id = WebConsumer.Inspection.WInspectionImages.Save(image.ServerID, image.Inspection.ServerID, image.InspectionResult.SeverId, image.ImageFile.Name);
            return image.Id;
        }

        public static void Delete(EInspectionImage image)
        {
            WebConsumer.Inspection.WInspectionImages.Delete(image.Id,image.FileName);
        }

        #endregion

        private static List<EInspectionImage> ParseDatatableImages(DataTable dt)
        {
            List<EInspectionImage> _InspectionImages = new List<EInspectionImage>();

            foreach (DataRow dr in dt.Rows)
            {
                EInspectionImage _Image = new EInspectionImage();
                _Image.Id = ConvertHelper.ToInt32(dr["Vendor_InspVisit_XmissionStructures_ImageId"]);
                _Image.ServerID = ConvertHelper.ToInt32(dr["Vendor_InspVisit_XmissionStructures_ImageId"]);
                _Image.Inspection.Id = ConvertHelper.ToInt32(dr["Vendor_InspVisit_XMissionStructure_PoleFk"]);
                _Image.Inspection.ServerID = ConvertHelper.ToInt32(dr["Vendor_InspVisit_XMissionStructure_PoleFk"]);
                _Image.InspectionResult.Id = ConvertHelper.ToInt32(dr["Vendor_InspVisit_XmissionStructures_ResultFk"]);
                _Image.InspectionResult.SeverId= ConvertHelper.ToInt32(dr["Vendor_InspVisit_XmissionStructures_ResultFk"]);
                _Image.FileName = dr["filename"].ToString();
                _InspectionImages.Add(_Image);

            }
            return _InspectionImages;
        }




        public static List<EInspectionImage> GetByInspection(int id)
        {
            DataTable imagesDatatable = WebConsumer.Inspection.WInspectionImages.GetInspectionImageById(id).Tables[0];
            return ParseDatatableImages(imagesDatatable);
        }
    }
}
